/*
 * Copyright 2015 2dfire.com All right reserved. This software is the
 * confidential and proprietary information of 2dfire.com ("Confidential
 * Information"). You shall not disclose such Confidential Information and shall
 * use it only in accordance with the terms of the license agreement you entered
 * into with 2dfire.com.
 */

package com.mallcai.bigdata.ladon.utils.excel;

import com.alibaba.fastjson.JSON;
import com.mallcai.bigdata.ladon.dpl.vo.DataType;
import org.apache.commons.lang3.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.util.CellRangeAddress;

import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.mallcai.bigdata.ladon.utils.excel.CustomCellStyle.*;
import static java.nio.charset.StandardCharsets.UTF_16;

/**
 * described : 生成普通Excel的导出文件
 * 从头到尾生成
 * Created by DiLong on 2017-12-14 10:06.
 * Modify:
 */
public class GenExcelFile {
    private static DecimalFormat    doubleFormat   = new DecimalFormat("#0.00");
    private static SimpleDateFormat dateFormat     = new SimpleDateFormat("yyyy-MM-dd");
    private static SimpleDateFormat datetimeFormat = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");

    private final Map<String, CellStyle> styles;      //样式列表
    private final HSSFWorkbook           workbook;

    private Map<String, String> i18Map   = new HashMap<>();
    private int                 rowIndex = 0;   //数据开始位置

    public GenExcelFile() {

        workbook = new HSSFWorkbook();

        styles = new HashMap<>();
        styles.put(STYLE_HEADER, CustomCellStyle.initCellStyle(workbook));
        styles.put(STYLE_CELL, CustomCellStyle.initCellStyle(workbook));
        styles.put(STYLE_TITLE, CustomCellStyle.initTitleStyle(workbook));
        styles.put(STYLE_TIP, CustomCellStyle.initTipStyle(workbook));
    }

    /**
     * 添加一个Sheet页
     *
     * @param sheetName  String sheet名称
     * @param title      String 标题
     * @param tips       List
     * @param maxColSize int
     * @param blocks     List
     */
    public void addSheet(String sheetName,
                         String title,
                         List<String> tips,
                         int maxColSize,
                         List<ExcelBlock> blocks) {
        // 默认为sheet1
        if (StringUtils.isBlank(sheetName)) {
            sheetName = "sheet1";
        }

        Sheet sheet = workbook.createSheet(sheetName);
        if (maxColSize < 1) {
            maxColSize = 1;
        }

        rowIndex = 0;

        //绘制标题
        addMergedRegion(sheet, rowIndex, rowIndex, 0, maxColSize - 1, title, styles.get(STYLE_TITLE), (short) 30);
        rowIndex++;

        //绘制名称、查询条件等
        for (String tip : tips) {
            addMergedRegion(sheet, rowIndex, rowIndex, 0, maxColSize - 1, tip, styles.get(STYLE_TIP), (short) 23);
            rowIndex++;
        }
        rowIndex++; //空出一行

        for (ExcelBlock block : blocks) {
            //获取列个数
            int colSize = getColSize(block.getColumns());
            // 创建头部
            List<ComplexHead> leafColumns = getLeafColumn(block.getColumns()); //获取所有叶子表头

            //绘制标题
            addMergedRegion(sheet, rowIndex, rowIndex, 0, colSize - 1, block.getTitle(), styles.get(STYLE_TITLE), (short) 30);
            rowIndex++;

            //绘制头
            writeSimpleTitles(sheet, leafColumns, rowIndex, styles.get(STYLE_HEADER));
            rowIndex++; //获取表头深度

            List<Map<String, Object>> rows = block.getRows();
            if (rows != null) {
                if (block.getFootRows() != null) {
                    rows.addAll(block.getFootRows());
                }

                addData(sheet, leafColumns, rows, block.getColumnTypes(), true); //添加数据
            }

            rowIndex++; //空出一行
        }

    }

    public void addSheet(String sheetName,
                         String title,
                         List<String> tips,
                         List<ComplexHead> header,
                         List<Map<String, Object>> rows,
                         Map<String, DataType> columnTypes,
                         Map<String, String> fieldNameToShow) {
        addSheet(sheetName, title, tips, header, rows, columnTypes, fieldNameToShow, true);
    }

    /**
     * 添加一个Sheet页
     *
     * @param sheetName       String sheet名称
     * @param title           String 标题
     * @param tips            List tip列表
     * @param header          List 字段列表
     * @param rows            List 行数据
     * @param columnTypes     Map 字段对应的数据类型
     * @param fieldNameToShow Map 字段对应显示名称
     * @param translate       boolean 是否翻译
     */
    public void addSheet(String sheetName,
                         String title,
                         List<String> tips,
                         List<ComplexHead> header,
                         List<Map<String, Object>> rows,
                         Map<String, DataType> columnTypes,
                         Map<String, String> fieldNameToShow,
                         boolean translate) {
        // 默认为sheet1
        if (StringUtils.isBlank(sheetName)) {
            sheetName = "sheet1";
        }

        Sheet sheet = workbook.createSheet(sheetName);

        rowIndex = 0;

        //获取列个数
        int colSize = getColSize(header);

        //绘制标题
        addMergedRegion(sheet, rowIndex, rowIndex, 0, colSize - 1, title, styles.get(STYLE_TITLE), (short) 30);
        rowIndex++;

        //绘制名称、查询条件等
        for (String tip : tips) {
            addMergedRegion(sheet, rowIndex, rowIndex, 0, colSize - 1, tip, styles.get(STYLE_TIP), (short) 23);
            rowIndex++;
        }

        // 创建头部
        writeTitles(sheet, fieldNameToShow, header, rowIndex, styles.get(STYLE_HEADER), colSize, null);
        rowIndex += getHeadDeep(header); //获取表头深度

        try {
            mergeColumns(sheet);       //合并列
            mergeRows(sheet, colSize); //合并行
        } catch (Exception e) {
            System.err.printf("合并行失败, title: %s, tips: %s, err: %s", title, JSON.toJSONString(tips), e);
        }
        freezeTitle(sheet);        //冻结行

        List<ComplexHead> leafColumns = getLeafColumn(header); //获取所有叶子表头

        addData(sheet, leafColumns, rows, columnTypes, translate);

        // 根据内容自动调整列宽
        setAutoSizeColumn(sheet, leafColumns.size());
    }

    private List<ComplexHead> filterHeaders(List<ComplexHead> header, Map<String, String> fieldNameToShow, List<String> exportColumns) {
        List<ComplexHead> columns = new ArrayList<>();
        for (ComplexHead col : header) {
            if (col.haveChildren()) {
                List<ComplexHead> children = filterHeaders(col.getChildren(), fieldNameToShow, exportColumns);
                if (children != null && children.size() > 0) {
                    columns.add(new ComplexHead(col.getName(), children));
                }
            } else {
                String tmp = fieldNameToShow.get(col.getName());
                if (StringUtils.isBlank(tmp)) {
                    tmp = col.getName();
                }
                if (exportColumns.contains(tmp)) {
                    columns.add(new ComplexHead(col.getName()));
                }
            }
        }
        return columns;
    }

    /**
     * 增加数据内容
     *
     * @param sheet       Sheet
     * @param leafColumns List  叶子节点字段
     * @param rows        List  数据行
     * @param columnTypes Map   数据类型
     * @param translate   boolean 是否翻译
     */
    private void addData(Sheet sheet, List<ComplexHead> leafColumns, List<Map<String, Object>> rows, Map<String, DataType> columnTypes, boolean translate) {

        int[] maxCellWidth = getMaxWidth(leafColumns, rows);  //获取每列的最大宽度

        //设置单元格内容
        for (Map<String, Object> oneRow : rows) {
            Row row = sheet.createRow(rowIndex++);
            int index = 0;
            DataType dt;
            for (ComplexHead c : leafColumns) {
                Cell cell = row.createCell(index);
                cell.setCellStyle(styles.get(STYLE_CELL));

                dt = columnTypes.get(c.getName()); //获取字段类型
                if (dt == null) {
                    dt = DataType.STRING;
                }

                setCellData(row, index, oneRow.get(c.getName()), dt, maxCellWidth[index], cell, sheet, translate);
                index++;
            }
        }
    }

    public HSSFWorkbook getWorkbook() {
        return workbook;
    }

    /**
     * 获取列数
     *
     * @param header List
     * @return int
     */
    private int getColSize(List<ComplexHead> header) {
        int size = 0;
        if (header == null || header.size() == 0) {
            return 0;
        }

        List<ComplexHead> children = new ArrayList<>();
        for (ComplexHead c : header) {
            if (c.haveChildren()) {
                children.addAll(c.getChildren());
            } else {
                size++;
            }
        }
        size += getColSize(children);
        return size;
    }

    /**
     * 冻结表头
     */
    private void freezeTitle(Sheet sheet) {
        int rowsCount = sheet.getPhysicalNumberOfRows();
        sheet.createFreezePane(0, rowsCount);
    }

    /**
     * 添加需要合并的单元格
     *
     * @param sheet    sheet页
     * @param firstRow 单元格起始行
     * @param lastRow  单元格结束行
     * @param firstCol 单元格起始列
     * @param lastCol  单元格结束列
     * @param content  内容
     */
    private void addMergedRegion(Sheet sheet, int firstRow, int lastRow, int firstCol, int lastCol,
                                 String content, CellStyle style, short height) {
        CellRangeAddress cra = new CellRangeAddress(firstRow, lastRow, firstCol, lastCol);
        sheet.addMergedRegion(cra);
        Row row = sheet.createRow(firstRow);
        row.setHeightInPoints(height);   //设置单元格高度
        Cell cell = row.createCell(firstCol);
        cell.setCellValue(content);
        cell.setCellStyle(style);
    }


    /**
     * 获取表头的深度
     *
     * @param header List
     * @return int
     */
    private int getHeadDeep(List<ComplexHead> header) {
        int deep = 1;

        List<ComplexHead> children = haveChildren(header);
        while (children.size() > 0) {
            List<ComplexHead> cc = haveChildren(children);
            children.clear();
            children.addAll(cc);
            deep++;
        }
        return deep;
    }


    //获取子节点
    private List<ComplexHead> haveChildren(List<ComplexHead> columns) {
        List<ComplexHead> haved = new ArrayList<>();
        columns.forEach((c) -> {
            if (c.haveChildren()) {
                haved.addAll(c.getChildren());
            }
        });
        return haved;
    }

    /**
     * 获取叶子节点,并保存原有顺序
     *
     * @param header List
     * @return List
     */
    private List<ComplexHead> getLeafColumn(List<ComplexHead> header) {
        List<ComplexHead> leaf = new ArrayList<>();

        if (header == null || header.size() == 0) {
            return leaf;
        }

        for (ComplexHead c : header) {
            leaf.addAll(getChildLeafColumn(c));
        }
        return leaf;
    }

    private List<ComplexHead> getChildLeafColumn(ComplexHead c) {
        List<ComplexHead> children = new ArrayList<>();

        if (c == null) {
            return children;
        }

        if (c.haveChildren()) {
            c.getChildren().forEach((v) -> children.addAll(getChildLeafColumn(v)));
        } else {
            children.add(c);
        }

        return children;
    }

    /**
     * 设置单元格数据
     *
     * @param row       指定行
     * @param i         行数
     * @param value     单元格值 cellValue
     * @param cell      单元格 HSSFCell对象
     * @param sheet     sheet HSSFSheet对象
     * @param translate boolean 是否翻译
     */
    private void setCellData(Row row, int i, Object value, DataType dataType, int maxWidth, Cell cell, Sheet sheet, boolean translate) {
        //设置单元格宽度，是文字能够全部显示
        sheet.setColumnWidth(i, maxWidth);    //设置单元格宽度
        row.setHeightInPoints((short) (20));   //设置单元格高度

        if (value == null) {
            cell.setCellValue("");
            return;
        }
        String txtValue = String.valueOf(value);
        switch (dataType) {
            case BOOLEAN:
                cell.setCellValue(Boolean.valueOf(txtValue));
                break;
            case DATE:
                Date date = (Date) value;
                cell.setCellValue(dateFormat.format(date));
                break;
            case DATETIME:
                Date datetime = (Date) value;
                cell.setCellValue(datetimeFormat.format(datetime));
                break;
            case BIG_DECIMAL:
            case DOUBLE:
            case FLOAT:
            case LONG:
                cell.setCellValue(doubleFormat.format(Double.parseDouble(txtValue)));
                break;
            case INTEGER:
                cell.setCellValue(Integer.parseInt(txtValue));
                break;
            case STRING:
                cell.setCellValue(txtValue);
                break;
            case OBJECT:
            default:
                cell.setCellValue(String.valueOf(value));
                break;
        }

    }

    private void writeSimpleTitles(Sheet sheet, List<ComplexHead> columns, int rowIndex, CellStyle style) {
        Row row = sheet.getRow(rowIndex) == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);
        int colIndex = 0;
        for (ComplexHead c : columns) {
            Cell cell = row.createCell(colIndex++);
            cell.setCellStyle(style);
            cell.setCellValue(c.getName());
        }
    }

    /**
     * 写入标题
     *
     * @param columns     List   复合列头
     * @param rowIndex    int    行号
     * @param parentTitle String 父节点名称
     */
    private void writeTitles(Sheet sheet, Map<String, String> fieldNameToShow, List<ComplexHead> columns, int rowIndex, CellStyle style, int maxCol, String parentTitle) {
        Iterator<ComplexHead> columnIterator = columns.iterator();
        Row row = sheet.getRow(rowIndex) == null ? sheet.createRow(rowIndex) : sheet.getRow(rowIndex);
        Row lastRow = rowIndex == 0 ? null : sheet.getRow(rowIndex - 1);

        int colIndex = -1;
        // 遍历当前行，获取行最多的单元格的个数，因为如果之前行之前有单元格是空的，会对列索引造成问题
        int currentMaxColumns = 0;
        for (int x = 0; x < rowIndex; x++) {
            Row rr = sheet.getRow(x);
            if (rr.getPhysicalNumberOfCells() > currentMaxColumns) {
                currentMaxColumns = rr.getPhysicalNumberOfCells();
            }
        }

        // 查找上一级的列开始位置
        if (lastRow != null && parentTitle != null) {
            for (int i = 0; i < currentMaxColumns; i++) {
                if (lastRow.getCell(i) != null && lastRow.getCell(i).getStringCellValue() != null && lastRow.getCell(i).getStringCellValue().equals(parentTitle)) {
                    colIndex = i;
                    break;
                }
            }
        }
        colIndex = colIndex == -1 ? 0 : colIndex;

        Cell cell;

        while (columnIterator.hasNext()) {

            ComplexHead e = columnIterator.next();
            // 获取子节点数量
            int count = getSubNodesCount(e);
            // 没有子节点就是叶子节点，如果子节点为0，将count设置为1，是为了能够让下面的for循环执行一次，将标题写入
            if (count == 0) {
                count = 1;
            }

            // 根据子节点的数量，写入相应数量的父节点的名称，待完成后合并列
            for (int i = 0; i < count; i++) {
                String tmp = e.getName();
                if (fieldNameToShow.containsKey(tmp)) {
                    tmp = fieldNameToShow.get(e.getName());
                }

                cell = row.createCell(colIndex++);
                cell.setCellStyle(style);
                cell.setCellValue(tmp);
                // 判断是否写到最后一个父节点名称
                if (i == count - 1) {
                    //统一风格
                    formatStyle(colIndex, maxCol, row, style);

                    // 如果有子节点,递归写入子节点
                    if (e.getChildren() != null && e.getChildren().size() > 0) {
                        writeTitles(sheet, fieldNameToShow, e.getChildren(), rowIndex + 1, style, maxCol, tmp);
                    }
                }
            }
        }
    }

    ///设置单元格风格
    private void formatStyle(int startCel, int endCel, Row row, CellStyle style) {
        if (endCel <= 0 || startCel >= endCel) {
            return;
        }

        Cell cell;
        for (int i = startCel; i < endCel; i++) {
            cell = row.createCell(i);
            cell.setCellStyle(style);
            cell.setCellValue("");
        }
    }

    /**
     * 获取当前节点的子节点数量
     *
     * @param column ComplexHead
     * @return int
     */
    private int getSubNodesCount(ComplexHead column) {

        if (column.getChildren() == null || column.getChildren().size() == 0) {
            return 0;
        }

        Iterator<ComplexHead> it = column.getChildren().iterator();
        int count = column.getChildren().size();
        while (it.hasNext()) {
            int c = getSubNodesCount(it.next());
            count += c > 0 ? c - 1 : c;
        }
        return count;
    }

    /**
     * 获取单元格最大宽度
     *
     * @param columns List<ComplexHead>
     * @param rows    Map
     * @return int[]
     */
    private int[] getMaxWidth(List<ComplexHead> columns, List<Map<String, Object>> rows) {
        List<String> strHeads = new ArrayList<>();
        for (ComplexHead c : columns) {
            if (c.haveChildren()) {
                c.getChildren().forEach((v) -> strHeads.add(v.getName()));
            } else {
                strHeads.add(c.getName());
            }
        }
        int[] ret = new int[strHeads.size()];

        //获取头部的宽度
        int i = 0;
        for (String c : strHeads) {
            try {
                ret[i] = StringUtils.trimToEmpty(c).getBytes(UTF_16).length;
            } catch (Exception e) {
                ret[i] = 50;
            }
            i++;
        }

        //内容的最大宽度
        for (Map<String, Object> row : rows) {
            i = 0;
            for (String c : strHeads) {
                String val = String.valueOf(row.get(c));
                int tmp;
                try {
                    tmp = StringUtils.trimToEmpty(val).getBytes(UTF_16).length;
                } catch (Exception e) {
                    tmp = StringUtils.trimToEmpty(val).length();
                }
                if (tmp > ret[i]) {
                    ret[i] = tmp;
                }
                i++;
            }
        }

        //获取实际长度
        for (i = 0; i < ret.length; i++) {
            int size = ret[i];
            if (size > 50) {
                size = 50;
            }
            ret[i] = (size + 6) * 256;
        }

        return ret;
    }

    /**
     * 合并行
     */
    private void mergeRows(Sheet sheet, int maxCols) {
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();
        Row row;

        Cell cell;
        int rowSpan;
        for (int c = 0; c < maxCols; c++) {
            rowSpan = 0;
            for (int r = rowsCount - 1; r > -1; r--) {
                row = sheet.getRow(r);
                cell = row.getCell(c);
                if (cell != null && StringUtils.isNotBlank(cell.getStringCellValue()) && r == rowsCount - 1) {
                    break;
                } else if (cell != null && StringUtils.isNotBlank(cell.getStringCellValue()) && r != rowsCount - 1) {
                    // 合并列
                    sheet.addMergedRegion(new CellRangeAddress(rowsCount - rowSpan - 1, rowsCount - 1, c, c));
                    break;
                } else {
                    // 行合并数+1
                    rowSpan++;
                }
            }
        }
    }

    /**
     * 设置Excel列自动调整列宽
     *
     * @param sheet   Sheet
     * @param colSize int 列数量
     */
    private void setAutoSizeColumn(Sheet sheet, int colSize) {
        sheet.setColumnWidth(0, 15 * 256); // 第一列设置15个字符
        for (int i = 1; i < colSize; i++) {
            sheet.autoSizeColumn(i, true);
        }
    }

    /**
     * 合并列
     */
    private void mergeColumns(Sheet sheet) {
        // 行数
        int rowsCount = sheet.getPhysicalNumberOfRows();

        Row row;
        Cell cell1, cell2;

        int colSpan;

        for (int r = 0; r < rowsCount; r++) {
            // 重置
            colSpan = 0;
            row = sheet.getRow(r);
            // 列数
            int colsCount = row.getPhysicalNumberOfCells();
            for (int c = 0; c < colsCount; c++) {
                cell1 = row.getCell(c);
                cell2 = row.getCell(c + 1);
                if (cell1 == null || StringUtils.isBlank(cell1.getStringCellValue())) {// 如果当前单元格是空的，跳过，继续当前行的后一个单元格查找
                    if (c == colsCount - 1) {
                        break;
                    } else {
                        continue;
                    }
                }
                if (cell2 == null || StringUtils.isBlank(cell1.getStringCellValue())) {// 说明当前行已经到最后一个单元格了
                    if (colSpan >= 1) {// 判断colSpan是否大于等于1，大于1就要合并了
                        // 合并行中连续相同的值的单元格
                        sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                        break;
                    }
                }

                if (cell2 != null && StringUtils.isNotBlank(cell1.getStringCellValue())) {
                    // 如果当前单元格和下一个单元格内容相同，那么colSpan加1
                    if (cell1.getStringCellValue().equals(cell2.getStringCellValue())) {
                        colSpan++;
                    } else {
                        // 如果当前单元格和下一个不等，那么判断colSpan是否大于等于1
                        if (colSpan >= 1) {
                            // 合并行中连续相同的值的单元格
                            sheet.addMergedRegion(new CellRangeAddress(r, r, c - colSpan, c));
                            // 合并后重置colSpan
                            colSpan = 0;
                        }
                    }
                }

            }
        }
    }
}
